How to Add “All” or “N/A” Options to Combo box

TGoldenEye

How to Add <All> or <N/A> Options to Combo box

A combo box is well known in the MS Access form. The combo box can be a Lookup field from a table. You can see more details at: How to Create Lookup Field in Table for MS Access here.  The combo box can also be a stand-alone combo box on the Access form. In this How To, I will show to add the <All> or <N/A> option on the list of combo box. The selection option: <All> or <N/A> is not listed on the table, but we can add on top of the list in the combo box.

I have created a from below and added the combo box for selecting the state. I will not show how to create the combo box on this How To. If you need to learn more on how to create the combo box, you can click on this link: Working with Combo Box.

In most cases best to perform the telephone interview which will help measure the skills, quaification and the style of the chiropractor as well as demand office consultation to find sildenafil mastercard out more about how the chiropractor practice. An English doctor named Edward Jenner first discovered that the development of smallpox can be avoided and getting viagra samples uk http://www.devensec.com/sustain/DEEC_Overview.pdf online becomes easier and helpful. Finpecia not only prevents hair from falling, but generic viagra also improves blood circulation below the area of your belt. The most online viagra soft common cause of this physical pressure is a common cause as is prostate cancer for instance.

The purpose of this form below is to select the state from the combo box dropdown list then display all customers who live in the selected state.  All states are listed in the combo box. This combo box has a Row Source from the Customers table, so there are duplicated states on the dropdown list as shown below, but not “<All>” option. We want to add <All> option on this combo box.

Under the Data tab of the property sheet of the combo box, you will see the Row Source in the picture below:

      Row Source  = SELECT [Customers].[ID], [Customers].[State] FROM Customers ORDER BY [State];

      Row Source Type     = Table/Query

      Limit To List       = Yes

      Allow Value List Edits = No

Actually, there are two fields/columns in the combo box. However, it displays only the State column (2nd column). The first column (ID) is hidden.

On the picture below, you will see the Column Count =2 and Column Widths =0”;1”. So the 1st column (ID) has the width =0 and does not display on the combo box.

I will demonstrate the steps below on how to add the “<All>” option to the combo box.

There are 2 methods to add “<All>” option into the combo box. 

Method 1: Using Union Query

Step #1 Open Query Builder

  • Open form in the form design view
  • Open the property sheet of the combo box
  • Open the Row Source of the combo box like the picture below

Step #2 Update Field/Column for combo box

  • Delete the 1st field or column (ID)
  • Delete the 3rd field or 3rd column for sorting Ascending

  • Click on Crosstab icon then click on Select icon
  • The Select Query now has the Total and Crosstab rows
  • The Group By is automatically added on the Total row
  • Select Ascending under the Sort row

Step #3 Update to Union Query

  • Right hand click on the Query Builder bar “Add <All> to Combo Box”
  • Select SQL View

  • Add “Union Select “<All>” from Customers” on the 2nd line after FROM Customers

  • Select View on the top left hand corner to view the result of Union Query
  • <All> option is now listed on the top of all states on the data sheet view
  • Save and Close this Query Builder

Step #4 Update the Combo Box Property

  • Click on the Format property of Combo box
  • Update Column Count = 1
  • Update Column Widths = 1” to display only State
  • Save and close the Form Design View and open the Form View

Now the <All> option displays on top of all states on the combo box. There is also no duplicate state on the list because we set the Group By on the Total row under the Query Builder. See picture below:

 

Method 2: Using VBA

The 2nd method is to use the VBA code to add the “<All>” option the combo box drop down list. You will need to follow the steps above from Step#1 to 4 except Step#3 for Update Union Query since we are not using the Union Query.  We need to create a Sup Procedure and call it under the Form On Load Event. 

Sub GetComboBoxList()
Dim strList, strSQL As String

strList = "<All>;"
With cboState
     With CurrentDb.OpenRecordset(.RowSource)
        Do Until .EOF
           strList = strList & !State & ";"
           .MoveNext
        Loop
End With
.RowSourceType = "Value List"
.RowSource = strList
End With
End Sub

Then call the GetComboBoxList() Sup-Procedure on the Form On Load Event as shown below.

Private Sub Form_Load()
      Call GetComboBoxList
End Sub

Now the <All> option displays on top of all states on the combo box. There is also no duplicate state on the list.

Filter Data in Sub-Form by Using VBA for Combo box

  • Under the design view, open the property of combo box
  • Select Event tab on the Property Sheet
  • Select [Event Procedure] on After Update event
  • Click on (…) to open the VBA window

  • Enter code in the picture below to filter the sub-form to display the customers who live in the state that is selected from the combo box.

Example of Selecting “CA” on the Combo box state.

Example of Selecting <All> option

All customers will display on the sub-form after selecting the <All> option as shown in the picture below.

 

Related posts